import pandas as pd  
import sqlite3

import numpy as np  
import random  
from datetime import datetime, timedelta  
 
df = pd.read_csv('diabetes_filtered.csv')

data_to_insert = df.iloc[49:1050]  
 
random.seed(42)  
 
def generate_patient_id():  
    return random.randint(10000000, 99999999)  

def generate_random_timestamp(start_date, end_date):  
    start_timestamp = int(start_date.timestamp())  
    end_timestamp = int(end_date.timestamp())  
    random_timestamp = random.randint(start_timestamp, end_timestamp)  
    return datetime.fromtimestamp(random_timestamp)  
  
conn = sqlite3.connect('health_records.db')  
  
conn.execute('''CREATE TABLE IF NOT EXISTS records (  
                    id INTEGER PRIMARY KEY,
                    idcard char(18),  
                    timestamp timestamp,  -- 改为TEXT以存储字符串格式的时间戳  
                    gender INTEGER,  
                    blood_glucose_level INTEGER,  
                    HbA1c_level INTEGER,  
                    hypertension INTEGER,  
                    bmi REAL,  
                    heart_disease REAL,  
                    age INTEGER,  
                    diabetes BLOB,
                    smoking_history INTEGER  
                )''')  
 
start_date = datetime(2024, 1, 1)  
end_date = datetime(2024, 11, 8)  
  
for index, row in data_to_insert.iterrows():  
    idcard = generate_patient_id()
    timestamp = generate_random_timestamp(start_date, end_date)  
  
    timestamp_str = timestamp.strftime('%Y-%m-%d %H:%M:%S')  
  
    conn.execute('''INSERT INTO records (idcard, timestamp, gender, blood_glucose_level, HbA1c_level,   
                    hypertension, bmi, age, diabetes, smoking_history, heart_disease)   
                     VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)''',
                 (idcard, timestamp_str, row['gender'], row['blood_glucose_level'], row['HbA1c_level'],
                  row['hypertension'], row['bmi'], row['age'], row['diabetes'], row['smoking_history'], row['heart_disease']))
 
conn.commit()  
conn.close()